CREATE VIEW [dbo].[vSoaGroupMemberSummaryRelationship]
WITH SCHEMABINDING
AS
    SELECT  CAST(SUBSTRING('RELATIONSHIP-' + [r].[ID] + ':'
                           + CASE WHEN [r].[TARGET_ID] > ''
                                  THEN [r].[TARGET_ID]
                                  ELSE [r].[TARGET_NAME]
                             END, 1, 50) AS VARCHAR(50)) AS [GroupMemberId],
            CAST('RELATIONSHIP-' + [r].[ID] AS VARCHAR(50)) AS [GroupId],
            [r].[TARGET_ID] AS [PartyId],
            CAST([r].[TARGET_NAME] AS VARCHAR(70)) AS [PartyName],
            [r].[SEQN]
      FROM  [dbo].[Relationship] AS r



GO
CREATE UNIQUE CLUSTERED INDEX [PK_vSoaGroupMemberSummaryRelationship] ON [dbo].[vSoaGroupMemberSummaryRelationship] ([SEQN]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_vSoaGroupMemberSummaryRelationship_GroupId] ON [dbo].[vSoaGroupMemberSummaryRelationship] ([GroupId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_vSoaGroupMemberSummaryRelationship_GroupMemberId] ON [dbo].[vSoaGroupMemberSummaryRelationship] ([GroupMemberId]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_vSoaGroupMemberSummaryRelationship_PartyId] ON [dbo].[vSoaGroupMemberSummaryRelationship] ([PartyId]) ON [PRIMARY]
GO